/**
 * AddTakenProject class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.AddTakenProjectDTO;
import pits.form.AddTakenProjectForm;
import pits.util.Logger;

public class AddTakenProjectDAO {

	public AddTakenProjectDTO getData() {
		AddTakenProjectDTO result = new AddTakenProjectDTO();
		List<String> companyNames = new ArrayList<String>();
		List<Integer> companyIDs = new ArrayList<Integer>();

		List<List<String>> departNames = new ArrayList<List<String>>();
		List<List<Integer>> departIDs = new ArrayList<List<Integer>>();

		List<String> eachComDepNames = null;
		List<Integer> eachComDepIDs = null;

		List<List<List<String>>> projectNames = new ArrayList<List<List<String>>>();
		List<List<List<Integer>>> projectIDs = new ArrayList<List<List<Integer>>>();

		List<List<String>> eachComProjectNames = null;
		List<List<Integer>> eachComProjectIDs = null;

		List<String> eachDepProjectNames = null;
		List<Integer> eachDepProjectIDs = null;

		List<String> roleNames = new ArrayList<String>();
		List<Integer> roleIDs = new ArrayList<Integer>();

		Connection conn = null;
		String query = null;
		PreparedStatement sqlStatement = null;
		ResultSet comResultSet = null;
		ResultSet departResultSet = null;
		ResultSet projectResultSet = null;
		ResultSet roleResultSet = null;

		try {
			query = "SELECT * FROM company WHERE isDeleted=0";

			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);

			// new Logger().write(query);

			comResultSet = sqlStatement.executeQuery();

			while (comResultSet.next()) {
				query = "SELECT * FROM department" + " WHERE CompanyID=" + comResultSet.getInt("CompanyID")
				        + " AND isDeleted=0";

				// new Logger().write(query);

				sqlStatement = conn.prepareStatement(query);
				departResultSet = sqlStatement.executeQuery();

				eachComDepNames = new ArrayList<String>();
				eachComDepIDs = new ArrayList<Integer>();
				eachComProjectNames = new ArrayList<List<String>>();
				eachComProjectIDs = new ArrayList<List<Integer>>();

				while (departResultSet.next()) {
					// new
					// Logger().write(departResultSet.getString("DepartName"));
					eachComDepNames.add(departResultSet.getString("DepartName"));
					eachComDepIDs.add(departResultSet.getInt("DepartID"));

					query = "SELECT * FROM project " + "WHERE DepartID=" + departResultSet.getInt("DepartID") + " "
					        + "AND isDeleted=0";
					// new pits.util.Logger().write(query);

					sqlStatement = conn.prepareStatement(query);
					projectResultSet = sqlStatement.executeQuery();

					eachDepProjectNames = new ArrayList<String>();
					eachDepProjectIDs = new ArrayList<Integer>();

					while (projectResultSet.next()) {
						// new
						// Logger().write(projectResultSet.getString("Name"));
						eachDepProjectNames.add(projectResultSet.getString("ProjectName"));
						eachDepProjectIDs.add(projectResultSet.getInt("ProjectID"));
					}

					eachComProjectNames.add(eachDepProjectNames);
					eachComProjectIDs.add(eachDepProjectIDs);

					query = "SELECT * FROM project " + "WHERE DepartID=" + departResultSet.getInt("DepartID") + " "
					        + "AND isDeleted=0";
					// new pits.util.Logger().write(query);

					sqlStatement = conn.prepareStatement(query);

				}
				// new Logger().write(comResultSet.getString("ComName"));
				companyNames.add(comResultSet.getString("ComName"));
				companyIDs.add(comResultSet.getInt("CompanyID"));
				departNames.add(eachComDepNames);
				departIDs.add(eachComDepIDs);
				projectNames.add(eachComProjectNames);
				projectIDs.add(eachComProjectIDs);
			}

			query = "SELECT * FROM role WHERE isDeleted = 0";
			sqlStatement = conn.prepareStatement(query);
			roleResultSet = sqlStatement.executeQuery();

			while (roleResultSet.next()) {
				roleIDs.add(roleResultSet.getInt("RoleID"));
				roleNames.add(roleResultSet.getString("Role"));
			}

			result.setRoleIDs(roleIDs);
			result.setRoleNames(roleNames);

			// new Logger().writeln("getCommonData: done");

		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		result.setCompanyNames(companyNames);
		result.setCompanyIDs(companyIDs);
		result.setDepartNames(departNames);
		result.setDepartIDs(departIDs);
		result.setProjectNames(projectNames);
		result.setProjectIDs(projectIDs);

		// new Logger().writeln("getData: done");

		return result;
	}

	public void update(AddTakenProjectForm formAddTakenProject) {

		Connection conn = null;
		String query = null;
		PreparedStatement sqlStatement = null;

		try {
			conn = pits.util.Connection.getConnection();

			if (formAddTakenProject.getRoleID() == null) {

				query = "INSERT INTO Role (Role, isDeleted) " + "VALUE ('" + formAddTakenProject.getRoleName()
				        + "', 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = conn.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM Role " + "WHERE Role='" + formAddTakenProject.getRoleName() + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				ResultSet roleRS = null;
				sqlStatement = conn.prepareStatement(query);
				roleRS = sqlStatement.executeQuery();
				roleRS.next();

				query = "INSERT INTO projectexp " + "VALUES (" + formAddTakenProject.getProjectID() + ", "
				        + formAddTakenProject.getEmployeeID() + ", " + roleRS.getInt("RoleID") + ", " + "0" + ")";

			} else {
				query = "INSERT IGNORE projectexp " + "VALUES (" + formAddTakenProject.getProjectID() + ", "
				        + formAddTakenProject.getEmployeeID() + ", " + formAddTakenProject.getRoleID() + ", " + "0"
				        + ")";
			}
			// new pits.util.Logger().writeln(query);
			sqlStatement = conn.prepareStatement(query);
			sqlStatement.executeUpdate();
		} catch (SQLException sqlEx) {
			new pits.util.Logger().writeln(sqlEx.toString());
		} finally {
			try {
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln(sqlEx.toString());
			}
		}
	}
}
